package my.main;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import my.bean.Dbcolumn;
import my.util.JDBCUtil;
import my.util.JdbcProperties;

public class ColForMysql {

	public List<Dbcolumn> getCol(String table) throws SQLException {
		Connection conn = JDBCUtil.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Dbcolumn> colList = new ArrayList<Dbcolumn>();
		try {
			if (JdbcProperties.driver.contains("oracle")) {
				//oracle
//				pstmt = conn.prepareStatement("SELECT acc.COLUMN_NAME, atc.DATA_TYPE, acc.COMMENTS  FROM user_col_comments acc left join user_tab_cols atc on acc.COLUMN_NAME =atc.COLUMN_NAME  WHERE acc.table_name = '" + table.toUpperCase() +"' and atc.TABLE_NAME = '" + table.toUpperCase() + "'");
				pstmt = conn.prepareStatement("SELECT acc.COLUMN_NAME, atc.DATA_TYPE, acc.COMMENTS, ucc.column_name FROM user_col_comments acc left join user_tab_cols atc " + 
		            " on acc.COLUMN_NAME = atc.COLUMN_NAME left join user_cons_columns ucc on atc.TABLE_NAME = ucc.table_name left join user_constraints uc on ucc.constraint_name = uc.constraint_name "+
		            " WHERE acc.table_name = '"+table.toUpperCase()+"' and atc.TABLE_NAME = '"+table.toUpperCase()+"' and uc.constraint_type = 'P'");
				rs = pstmt.executeQuery();
				while (rs.next()) {
					Dbcolumn col = new Dbcolumn();
					col.setColName(rs.getString(1));
					String type = rs.getString(2);
					//根据数据库表结构判断对java的类型
					if (type.contains("CHAR")) {
						col.setColType("String");
					} else if (type.contains("int")) {
						col.setColType("NUMBER");
					} else if(type.equals("double")){
						col.setColType("Double");
					} else if (type.contains("DATE") || type.equals("datetime") || type.equals("timestamp")) {
						/*
						 * DATE 
						 * 显示格式：YYYY-MM-DD
						 * TIMESTAMP   DATETIME
						 * 显示格式：YYYY-MM-DD HH:MM:SS
						 */
						col.setColType("Date");
					} else {
						col.setColType("String");
					}
					col.setComments(rs.getString(3));
					String key = rs.getString(4);
					col.setPriKey(key);
					if(col.getColName().equals(key)){
						col.setPriKeyType(col.getColType());
					}
					colList.add(col);
				}
				return colList;
			} else {
				//mysql
				pstmt = conn.prepareStatement("desc " + table);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					Dbcolumn col = new Dbcolumn();
					col.setColName(rs.getString(1));
					String type = rs.getString(2);
					//根据数据库表结构判断对java的类型
					if (type.contains("char")) {
						col.setColType("String");
					} else if (type.contains("int")) {
						col.setColType("Integer");
					} else if(type.equals("double")){
						col.setColType("Double");
					} else if (type.contains("date") || type.equals("datetime") || type.equals("timestamp")) {
						/*
						 * DATE 
						 * 显示格式：YYYY-MM-DD
						 * TIMESTAMP   DATETIME
						 * 显示格式：YYYY-MM-DD HH:MM:SS
						 */
						col.setColType("Date");
					} else {
						col.setColType("String");
					}
					String key = rs.getString(4);
					if("PRI".equals(key)){
						col.setPriKey(col.getColName());
						col.setPriKeyType(col.getColType());
					}
					colList.add(col);
				}
				return colList;
			}
		} finally {
			close(pstmt);
			close(rs);
			JDBCUtil.closeConnection();
		}
	}

	private void close(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private void close(PreparedStatement pstmt) {
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
